<?php

namespace App\Repositories;

use App\Enums\MallEnum;
use App\Enums\GameEnum;
use App\Enums\GiftTypeEnum;
use App\Services\Tools\DateTime;
use Illuminate\Support\Facades\DB;

class DataStatisticsRepository
{
    //日数据统计
    public function dayDataStatistics($date)
    {
        $model = app('model_everyday_data_statistics');

        $timeRange = [strtotime($date . ' 0:0:0'), strtotime($date . ' 23:59:59')];
        $data = [
            'register_total'    => $this->queryRegisterUserNum($timeRange),
            'recharge_total'    => $this->queryRechargeMoney($timeRange),
            'serial_total'      => $this->querySerial($timeRange),
            'withdraw'          => $this->calcDayWithDraw($timeRange[0],$timeRange[1]),
            'active'            => $this->calcDayActive($timeRange[0],$timeRange[1]),
            'send_red_packet'   => $this->calcDaySendRed($timeRange[0],$timeRange[1])/100,
            'receive_red_packet'=> $this->calcDayReceiveRed($timeRange[0],$timeRange[1])/100,
            'give_gift'         => $this->calcDayGiveGift($timeRange[0],$timeRange[1])/100,
        ];
        $record = $model->where('date', $date)->first();
        if ($record) {
            $model->where('id', $record->id)->update($data);
        } else {
            $data['date'] = $date;
            $model->insert($data);
        }
    }

    //查询注册用户数
    private function queryRegisterUserNum($timeRange)
    {
        return app('model_user')->whereBetween('register_time', $timeRange)->count();
    }

    //查询充值金额
    private function queryRechargeMoney($timeRange)
    {
        return app('model_user_recharge')
            ->whereBetween('pay_time', $timeRange)
            ->where('type','<>',3)
            ->where('status', 2)->sum('money');
    }

    //查询流水（送礼金币）
    private function querySerial($timeRange)
    {
        return app('model_give_gift')->whereBetween('add_time', $timeRange)->sum('total_price');
    }

    //计算房间流水变化率
    public function calculateRoomSerialChangeRate($lastSerials, $thisSerials)
    {
        if ($lastSerials && $thisSerials) {
            $serialChangeRate = [];
            foreach ($thisSerials as $k => &$v) {
//                $serialChangeRate[$k] = $this->calculateChangeRate($lastSerials[$k], $v);
                $serialChangeRate[$k] = $this->calculateChangeValue($lastSerials[$k], $v);
            }
        } else {
            $serialChangeRate = $thisSerials;
            foreach ($serialChangeRate as &$v) {
                $v = '';
            }
        }
        return $serialChangeRate;
    }

    /**
     * 计算变化率
     * @param int $lastSerial 上期流水
     * @param int $thisSerial 本期流水
     * @return string
     */
    public function calculateChangeRate($lastSerial, $thisSerial)
    {
        if ($lastSerial == 0 || $thisSerial == 0) {
            $changeRate = '';
        } else {
            $changeRate = round(($thisSerial - $lastSerial) / $lastSerial, 4);
            if ($changeRate >= 0) {
                $changeDesc = '增';
            } else {
                $changeDesc = '降';
            }
            $changeRate = $changeDesc . abs(round($changeRate * 100, 2)) . '%';
        }
        return $changeRate;
    }

    /**
     * 计算变化值
     * @param int $lastSerial 上期流水
     * @param int $thisSerial 本期流水
     * @return string
     */
    public function calculateChangeValue($lastSerial, $thisSerial)
    {
        if ($lastSerial == 0 && $thisSerial == 0) {
            $changeRate = '';
        } else {
            $changeRate = round(($thisSerial - $lastSerial), 4);
            if ($changeRate >= 0) {
                $changeDesc = '+';
            } else {
                $changeDesc = '-';
            }
            $changeRate = $changeDesc . abs(round($changeRate, 2));
        }
        return $changeRate;
    }

    //系统今日流水统计
    public function systemTodaySerial($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        $data = [
            //房间礼物
            'room_gift_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('room_gift'), $timestampRange),
            //盲盒
            'blind_box_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('magic_gift_config'), $timestampRange),
            //许愿流星
            'lucky_tree_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('tree_gift'), $timestampRange),
            //抢糖
//            'scramble_sugar_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('candies'), $timestampRange),
            'scramble_sugar_serial' => 0,
            //霍克城堡
            'huoke_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('huoke'), $timestampRange),
            //星之许愿池
            'star_wishing_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('happy'), $timestampRange),
            //星际探险
            'ad_astra_serial' => $this->querySystemSerial(GiftTypeEnum::getGiftType('ad_astra'), $timestampRange),
        ];
        $record = app('model_system_everyday_serial')->select('id')->where('date', $date)->first();
        if ($record) {
            app('model_system_everyday_serial')->where('id', $record->id)->update($data);
        } else {
            $data['date'] = $date;
            app('model_system_everyday_serial')->insert($data);
        }
    }

    //房间今日流水统计（根据礼物类型下的礼物id进行统计）
    public function roomTodaySerial($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        //礼物id与类型的映射
        $giftTypeArr = [GiftTypeEnum::getGiftType('room_gift'), GiftTypeEnum::getGiftType('tree_gift'), GiftTypeEnum::getGiftType('candies'), GiftTypeEnum::getGiftType('huoke'), GiftTypeEnum::getGiftType('happy'), GiftTypeEnum::getGiftType('ad_astra'), GiftTypeEnum::getGiftType('magic_gift_config')];
        foreach ($giftTypeArr as $giftType) {
            $giftIds = app('model_gift')->getGiftIdsByType($giftType);
            foreach ($giftIds as $giftId) {
                $giftIdTypeMap[$giftId] = $giftType;
            }
        }

        //查询日期内所有送礼记录
        $roomSerialTotal = [];
        $giveGiftRecords = app('model_give_gift')
            ->select('room_id', 'gift_id', 'total_price')
            ->whereBetween('add_time', $timestampRange)
            ->where('room_id', '>', 0)
            ->get();

        foreach ($giveGiftRecords as $giveGiftRecord) {

            if (isset($giftIdTypeMap[$giveGiftRecord->gift_id])) {

                if (!isset($roomSerialTotal[$giveGiftRecord->room_id])) {
                    $roomSerialTotal[$giveGiftRecord->room_id] = [
                        'room_gift_serial' => 0,
                        'blind_box_serial' => 0,
                        'lucky_tree_serial' => 0,
                        'scramble_sugar_serial' => 0,
                        'huoke_serial' => 0,
                        'star_wishing_serial' => 0,
                        'ad_astra_serial' => 0,
                    ];
                }

                $giftType = $giftIdTypeMap[$giveGiftRecord->gift_id];

                switch ($giftType) {
                    case GiftTypeEnum::getGiftType('room_gift'):
                        $field = 'room_gift_serial';
                        break;
                    case GiftTypeEnum::getGiftType('magic_gift_config'):
                        $field = 'blind_box_serial';
                        break;
                    case GiftTypeEnum::getGiftType('tree_gift'):
                        $field = 'lucky_tree_serial';
                        break;
                    case GiftTypeEnum::getGiftType('candies'):
                        $field = 'scramble_sugar_serial';
                        break;
                    case GiftTypeEnum::getGiftType('huoke'):
                        $field = 'huoke_serial';
                        break;
                    case GiftTypeEnum::getGiftType('happy'):
                        $field = 'star_wishing_serial';
                        break;
                    case GiftTypeEnum::getGiftType('ad_astra'):
                        $field = 'ad_astra_serial';
                        break;
                }
                //临时停止统计
                if ($field=="scramble_sugar_serial"){
                    $giveGiftRecord->total_price=0;
                }
                $roomSerialTotal[$giveGiftRecord->room_id][$field] += $giveGiftRecord->total_price;
            }
        }

        //新增或更新统计数据
        $model = app('model_system_room_everyday_serial');

        foreach ($roomSerialTotal as $roomId => $data) {
            $record = $model->select('id')
                ->where('room_id', $roomId)
                ->where('date', $date)
                ->first();
            if ($record) {
                $model->where('id', $record->id)->update($data);
            } else {
                $data['room_id'] = $roomId;
                $data['date'] = $date;
                $model->insert($data);
            }
        }
    }

    //房间总流水统计
    public function roomSerial()
    {
        //方式一：根据房间每日流水汇总表统计
        $roomSerials = app('model_system_room_everyday_serial')
            ->select('room_id', DB::raw('sum(room_gift_serial) as room_gift_serial, sum(blind_box_serial) as blind_box_serial, sum(lucky_tree_serial) as lucky_tree_serial, sum(scramble_sugar_serial) as scramble_sugar_serial, sum(huoke_serial) as huoke_serial, sum(star_wishing_serial) as star_wishing_serial, sum(ad_astra_serial) as ad_astra_serial'))
            ->groupBy('room_id')
            ->orderBy('room_id')
            ->get()->toArray();
        foreach ($roomSerials as $key=>$roomSerial) {
            $record = app('model_system_room_serial')
                ->select('room_id')
                ->where('room_id', $roomSerial['room_id'])
                ->first();
            //临时停止计算
            $roomSerial['scramble_sugar_serial'] = 0 ;
            if ($record) {
                app('model_system_room_serial')->where('room_id', $roomSerial['room_id'])->update($roomSerial);
            } else {
                app('model_system_room_serial')->insert($roomSerial);
            }
        }

        //方式二：根据give_gift表统计
    }

    //根据礼物类别和时间段统计流水
    public function querySystemSerial($giftType, $timestampRange)
    {
        //查询类型下的礼物id
        $giftIds = app('model_gift')->getGiftIdsByType($giftType);
        $total = 0;
        if ($giftType == 36){
            for ($i = 0;$i <= 50; $i++){
                $limit = 30000;
                $skip = $i * $limit;
                $gives = app('model_give_gift')->newQuery()
                    ->select(['id','gift_id','total_price'])
                    ->whereBetween('add_time', $timestampRange)
                    ->orderBy('id','asc')
                    ->skip($skip)
                    ->take($limit)
                    ->get();
                if ($gives->isEmpty()){
                    unset($gives);
                    break;
                }
                foreach ($gives as $item){
                    if (in_array($item->gift_id,$giftIds)){
                        $total+= $item->total_price;
                    }
                }
                unset($gives);
            }
        }else{
            $total =  app('model_give_gift')
                ->whereIn('gift_id', $giftIds)
                ->whereBetween('add_time', $timestampRange)
                ->sum('total_price');
        }
        return $total;
    }

    //根据礼物类别和时间段统计流水
    public function querySystemSerialBy($giftType, $timestampRange)
    {
        //查询类型下的礼物id
        $giftIds = app('model_gift')->getGiftIdsByType($giftType);

        return app('model_give_gift')
            ->whereIn('gift_id', $giftIds)
            ->whereBetween('add_time', $timestampRange)
            ->sum('total_price');
    }

    //商城每日流水
    public function mallTodaySerial($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        //查询所有礼物分类
        $categoryIds = app('model_mall_categories')->getAllSubCateId(MallEnum::PRODUCT_CATE_ID_ALL);

        //按分类统计流水
        $categorySerials = app('model_mall_orders_history')
            ->select('category_id', DB::raw('sum(price) as serial_total'))
            ->whereIn('category_id', $categoryIds)
            ->whereBetween('add_time', $timestampRange)
            ->whereIn('type', [1, 2])
            ->groupBy('category_id')
            ->get()->toArray();

        //没有流水的分类也加一条流水为0的数据
        $haveDataCategoryIds = array_column($categorySerials, 'category_id');
        $noDataCategoryIds = array_diff($categoryIds, $haveDataCategoryIds);
        foreach ($noDataCategoryIds as $noDataCategoryId) {
            $categorySerials[] = ['category_id' => $noDataCategoryId, 'serial_total' => 0];
        }

        //新增或更新流水
        foreach ($categorySerials as $categorySerial) {

            $data = ['serial' => $categorySerial['serial_total']];

            $record = app('model_mall_everyday_serial')
                ->select('id')
                ->where('date', $date)
                ->where('product_cate_id', $categorySerial['category_id'])
                ->first();
            if ($record) {
                app('model_mall_everyday_serial')->where('id', $record->id)->update($data);
            } else {
                $data['date'] = $date;
                $data['product_cate_id'] = $categorySerial['category_id'];
                app('model_mall_everyday_serial')->insert($data);
            }
        }
    }

    //统计用户数
    public function statisticsUserCount()
    {
        $dateHour = date('Y-m-d H', strtotime("-1 hour"));
        [$date, $endHour] = explode(' ', $dateHour);
        $startHour = $endHour - 1 == -1 ? 23 : $endHour - 1;
        $timestampRange = [strtotime($dateHour . ':00:00'), strtotime($dateHour . ':59:59')];

        //注册人数
        $registerCount = app('model_user')->whereBetween('register_time', $timestampRange)->count();
        //登录用户
        $loginUsers = app('model_user_device')->whereBetween('add_time', $timestampRange)->groupBy('user_id')->get();
        //充值人数
        $rechargeCount = app('model_user_recharge')
            ->whereBetween('add_time', $timestampRange)
            ->where('status', 2)
            ->groupBy('user_id')->count();

        $data = [
            'register_count' => $registerCount,
            'login_count' => count($loginUsers),
            'recharge_count' => $rechargeCount,
        ];
        $record = app('model_statistics_user_count')
            ->select('id')
            ->where('date', $date)
            ->where('end_hour', $endHour)
            ->first();
        if ($record) {
            app('model_statistics_user_count')
                ->where('id', $record->id)
                ->update($data);
        } else {
            $data['date'] = $date;
            $data['start_hour'] = $startHour;
            $data['end_hour'] = $endHour;
            app('model_statistics_user_count')->insert($data);
        }
    }

    //统计用户金额
    public function statisticsUserAmount()
    {
        $date = date('Y-m-d');
        $timestampRange = [strtotime($date . ' 00:00:00'), strtotime($date . ' 23:59:59')];

        //充值金额
        $rechargeAmount = app('model_user_recharge')
            ->whereBetween('pay_time', $timestampRange)
            ->where('status', 2)
            ->where('type','<>',3)
            ->sum('money');
        //提现金额
        $withdrawAmount = app('model_user_withdraw')
            ->whereBetween('add_time', $timestampRange)
            ->where('status', 2)
            ->sum('money');

        $data = [
            'recharge_amount' => $rechargeAmount,
            'withdraw_amount' => $withdrawAmount,
        ];
        $record = app('model_statistics_user_amount')
            ->select('id')
            ->where('date', $date)
            ->first();
        if ($record) {
            app('model_statistics_user_amount')
                ->where('id', $record->id)
                ->update($data);
        } else {
            $data['date'] = $date;
            app('model_statistics_user_amount')->insert($data);
        }
    }

    //游戏活动用户数据（次数，人数，投入，产出）
    public function StatisticsGameUserData($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        //许愿流星
        $luckyTreeRecord = app('model_lucky_tree_log')
            ->select('user_id')
            ->whereBetween('add_time', $timestampRange)
            ->pluck('user_id')->toArray();
        $luckyTreeInputAndOutput = app('model_gold_price')
            ->select(DB::raw('sum(total_cost) as input, sum(number) as output'))
            ->whereBetween('add_time', $timestampRange)
            ->first();
        $data = [
            'game_count' => count($luckyTreeRecord),
            'people_count' => count(array_unique($luckyTreeRecord)),
            'input_gold' => $luckyTreeInputAndOutput->input ?? 0,
            'output_gold' => $luckyTreeInputAndOutput->output ?? 0,
        ];
        app('model_statistics_game_data')->add($date, GameEnum::GAME_NO_LUCKY_TREE, $data);

        //抢糖
        $sugarCount = app('model_game_sugar_info')
            ->whereBetween('create_time', $timestampRange)
            ->where('status', 2)->count();
        $sugarPeopleCount = app('model_game_sugar_join')
            ->whereBetween('create_time', $timestampRange)
            ->groupBy('user_id')
            ->count();
        $sugarInputAndOutput = app('model_game_sugar_record')
            ->select(DB::raw('sum(get_num) as input, sum(cost) as output'))
            ->whereBetween('create_time', $timestampRange)
            ->first();

        $data = [
            'game_count' => $sugarCount,
            'people_count' => $sugarPeopleCount,
            'input_gold' => $sugarInputAndOutput->input ?? 0,
            'output_gold' => $sugarInputAndOutput->output ?? 0,
        ];
        app('model_statistics_game_data')->add($date, GameEnum::GAME_NO_SUGAR, $data);

        //霍克城堡
        $data = [
            'game_count' => app('repo_account_check')->playHuokeTimeNum(),
            'people_count' => app('repo_account_check')->palyHuokeManNum(),
            'input_gold' => app('repo_account_check')->addGameData(),
            'output_gold' => app('repo_account_check')->subGameData(),
        ];
        app('model_statistics_game_data')->add($date, GameEnum::GAME_NO_HUOKE, $data);
    }

    //多种数据统计（推广系统平台数据统计）
    public function manyKinds($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        //查询所有新用户id
        $newUserIds = app('model_user')
            ->select('user_id')
            ->whereBetween('register_time', $timestampRange)
            ->pluck('user_id', 'user_id')->toArray();

        //查询所有渠道分期用户id
        $stageUserIds = app('model_user')
            ->select('channel_stage_id', 'user_id')
            ->where('channel_stage_id', '>', 0)
            ->pluck('channel_stage_id', 'user_id')->toArray();

        //查询所有渠道分期
        $stages = app('model_channel_stage')
            ->select('id as channel_stage_id', 'channel_id')
            ->get()->toArray();
        if (empty($stages)) return;
        $stages = array_column($stages, null, 'channel_stage_id');

        foreach ($stages as &$stage) {
            $stage['date'] = $date;
            $stage['day_active'] = 0;
            $stage['recharge_user_num'] = [];
            $stage['recharge_count'] = 0;
            $stage['recharge_amount'] = 0;
            $stage['lottery_amount'] = 0;
            $stage['give_gift_amount'] = 0;
            $stage['watch_live_duration'] = 0;
            $stage['next_day_online_num'] = 0;
            $stage['third_day_online_num'] = 0;
            $stage['seventh_day_online_num'] = 0;
            $stage['fifteenth_day_online_num'] = 0;
            $stage['thirtieth_day_online_num'] = 0;
        }
        //增加非渠道数据记录，渠道和分期id都为0
        foreach ($stages as $v) {
            $stages[0] = $v;
            break;
        }
        $stages[0]['channel_id'] = 0;
        $stages[0]['channel_stage_id'] = 0;

        //新老用户数据分别用一个二维数组保存所有统计数据
        $newUserData = $oldUserData = $stages;

        //日活用户
        $dayActive = $this->dayActive($timestampRange);
        foreach ($dayActive as $v) {
            if (isset($newUserIds[$v['user_id']])) {
                $newUserData[$v['channel_stage_id']]['day_active']++;
            } else {
                $oldUserData[$v['channel_stage_id']]['day_active']++;
            }
        }

        //充值记录
        $rechargeRecords = $this->rechargeRecord($timestampRange);
        foreach ($rechargeRecords as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['recharge_user_num'][$userId] = 0;
                $newUserData[$stageId]['recharge_count']++;
                $newUserData[$stageId]['recharge_amount'] += $v['money'];
            } else {
                $oldUserData[$stageId]['recharge_user_num'][$userId] = 0;
                $oldUserData[$stageId]['recharge_count']++;
                $oldUserData[$stageId]['recharge_amount'] += $v['money'];
            }
        }

        //抽奖金额
        //1.许愿树
        $luckyTree = $this->luckyTreeWaterDrop($timestampRange);
        foreach ($luckyTree as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['lottery_amount'] += $v['number'] * 200;
            } else {
                $oldUserData[$stageId]['lottery_amount'] += $v['number'] * 200;
            }
        }
        //2.霍克城堡
        $huoke = $this->huokeBuyBlueDiamond($timestampRange);
        foreach ($huoke as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['lottery_amount'] += $v['amount'];
            } else {
                $oldUserData[$stageId]['lottery_amount'] += $v['amount'];
            }
        }
        //3.抢糖
        $sugar = $this->sugarRecord($timestampRange);
        foreach ($sugar as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['lottery_amount'] += $v['cost'] * $v['gift_price'];
            } else {
                $oldUserData[$stageId]['lottery_amount'] += $v['cost'] * $v['gift_price'];
            }
        }

        //打赏金额
        $giveGifts = $this->giveGiftAmount($timestampRange);
        foreach ($giveGifts as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['give_gift_amount'] += $v['total_price'];
            } else {
                $oldUserData[$stageId]['give_gift_amount'] += $v['total_price'];
            }
        }

        //观看直播时长
        $watchLiveDuration = $this->watchLiveDuration($timestampRange);
        foreach ($watchLiveDuration as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['watch_live_duration'] += $v['watch_live_seconds'];
            } else {
                $oldUserData[$stageId]['watch_live_duration'] += $v['watch_live_seconds'];
            }
        }

        //用户留存
        $onlineUsers = $this->onlineUser($date);
        foreach ($onlineUsers as $v) {
            $userId = $v['user_id'];
            if (isset($newUserIds[$userId])) {
                if ($v['is_next_day_login'] == 1) {
                    $newUserData[$v['channel_stage_id']]['next_day_online_num']++;
                } else if ($v['is_third_day_login'] == 1) {
                    $newUserData[$v['channel_stage_id']]['third_day_online_num']++;
                } else if ($v['is_seven_day_login'] == 1) {
                    $newUserData[$v['channel_stage_id']]['seventh_day_online_num']++;
                } else if ($v['is_fifteen_day_login'] == 1) {
                    $newUserData[$v['channel_stage_id']]['fifteenth_day_online_num']++;
                } else if ($v['is_thirtieth_day_login'] == 1) {
                    $newUserData[$v['channel_stage_id']]['thirtieth_day_online_num']++;
                }
            } else {
                if ($v['is_next_day_login'] == 1) {
                    $oldUserData[$v['channel_stage_id']]['next_day_online_num']++;
                } else if ($v['is_third_day_login'] == 1) {
                    $oldUserData[$v['channel_stage_id']]['third_day_online_num']++;
                } else if ($v['is_seven_day_login'] == 1) {
                    $oldUserData[$v['channel_stage_id']]['seventh_day_online_num']++;
                } else if ($v['is_fifteen_day_login'] == 1) {
                    $oldUserData[$v['channel_stage_id']]['fifteenth_day_online_num']++;
                } else if ($v['is_thirtieth_day_login'] == 1) {
                    $oldUserData[$v['channel_stage_id']]['thirtieth_day_online_num']++;
                }
            }
        }

        foreach ($newUserData as $v) {
            $v['recharge_user_num'] = count($v['recharge_user_num']);
            $v['watch_live_duration'] = round($v['watch_live_duration']/3600, 1);
            $v['is_new_user'] = 1;
            $res = app('model_platform_everyday_data')->getRow('id', [
                'date' => $v['date'],
                'channel_id' => $v['channel_id'],
                'channel_stage_id' => $v['channel_stage_id'],
                'is_new_user' => $v['is_new_user'],
            ]);
            if ($res) {
                app('model_platform_everyday_data')->edit(['id' => $res->id], $v);
            } else {
                app('model_platform_everyday_data')->add($v);
            }
        }
        foreach ($oldUserData as $v) {
            $v['recharge_user_num'] = count($v['recharge_user_num']);
            $v['watch_live_duration'] = round($v['watch_live_duration']/3600, 1);
            $v['is_new_user'] = 0;
            $res = app('model_platform_everyday_data')->getRow('id', [
                'date' => $v['date'],
                'channel_id' => $v['channel_id'],
                'channel_stage_id' => $v['channel_stage_id'],
                'is_new_user' => $v['is_new_user'],
            ]);
            if ($res) {
                app('model_platform_everyday_data')->edit(['id' => $res->id], $v);
            } else {
                app('model_platform_everyday_data')->add($v);
            }
        }
    }

    //根据用户id查询渠道分期id
    private function getStageIdByUserId(&$stageUserIds, $userId)
    {
        if (isset($stageUserIds[$userId])) {
            $stageId = $stageUserIds[$userId];
        } else {
            $stageId = 0;
        }
        return $stageId;
    }

    //日活用户
    public function dayActive($timestampRange)
    {
        return app('model_user')
            ->select('user_id', 'channel_stage_id')
            ->whereBetween('last_login_time', $timestampRange)
            ->get()->toArray();
    }

    //充值记录
    public function rechargeRecord($timestampRange)
    {
        return app('model_user_recharge')
            ->select('user_id', 'money')
            ->whereBetween('pay_time', $timestampRange)
            ->where('status', 2)
            ->get()->toArray();
    }

    //许愿树购买水滴数量
    public function luckyTreeWaterDrop($timestampRange)
    {
        $datetimeRange = [date('Y-m-d H:i:s', $timestampRange[0]), date('Y-m-d H:i:s', $timestampRange[1])];
        return app('model_waterdrop_log')
            ->select('user_id', 'number')
            ->whereBetween('add_time', $datetimeRange)
            ->where('type', 1)
            ->get()->toArray();
    }

    //霍克城堡购买蓝钻
    public function huokeBuyBlueDiamond($timestampRange)
    {
        $datetimeRange = [date('Y-m-d H:i:s', $timestampRange[0]), date('Y-m-d H:i:s', $timestampRange[1])];
        return app('model_game_balance_log')
            ->select('user_id', 'amount')
            ->whereBetween('created_at', $datetimeRange)
            ->get()->toArray();
    }

    //抢糖记录
    public function sugarRecord($timestampRange)
    {
        return app('model_game_sugar_record')
            ->select('user_id', 'cost', 'gift_price')
            ->whereBetween('create_time', $timestampRange)
            ->where('profit', '<', 0)
            ->get()->toArray();
    }

    //打赏金额（送礼金额）
    public function giveGiftAmount($timestampRange)
    {
        return app('model_give_gift')
            ->select('user_id', 'total_price')
            ->whereBetween('add_time', $timestampRange)
            ->get()->toArray();
    }

    //观看直播时长
    public function watchLiveDuration($timestampRange)
    {
        $first = app('model_room_join_time')
            ->select('user_id', 'join_time', 'left_time')
            ->where([
                ['join_time', '>=', $timestampRange[0]],
                ['join_time', '<', $timestampRange[1]]
            ]);
        $data = app('model_room_join_time')
            ->select('user_id', 'join_time', 'left_time')
            ->where([
                ['join_time', '<=', $timestampRange[0]],
                ['join_time', '>=', $timestampRange[0]]
            ])->union($first)->get()->toArray();
        /*$data = app('model_room_join_time')
            ->select('user_id', 'join_time', 'left_time')
            ->where(function ($query) use ($timestampRange) {
                $query->where(function ($query2) use ($timestampRange) {
                    $query2->where('join_time', '>=', $timestampRange[0])
                        ->where('join_time', '<', $timestampRange[1]);
                    })
                    ->orWhere(function ($query3) use ($timestampRange) {
                        $query3->where('join_time', '<=', $timestampRange[0])
                            ->where('left_time', '>=', $timestampRange[0]);
                    });
            })
            ->get()->toArray();*/
        //计算统计时段内的观看直播时间
        foreach ($data as &$v) {
            $validStartTime = $v['join_time'] > $timestampRange[0] ? $v['join_time'] : $timestampRange[0];
            $validEndTime = $v['left_time'] < $timestampRange[1] ? $v['left_time'] : $timestampRange[1];
            $v['watch_live_seconds'] = $validEndTime - $validStartTime;
        }
        return $data;
    }

    //留存用户数
    public function onlineUser($date)
    {
        if (empty($date)) $date = date('Y-m-d');

        //查询第1/2/6/14/29天前注册的用户
        $daysArr = [1, 2, 6, 14, 29];
        $timestampRangeArr = [];
        foreach ($daysArr as $days) {
            $tmpDate = date('Y-m-d', strtotime("$date -$days day"));
            $timestampRangeArr[] = DateTime::getDateTimestampRange($tmpDate);
        }

        $userIds = [];
        foreach ($timestampRangeArr as $timestampRange) {
            $userIds += app('model_user')
                ->select('user_id')
                ->whereBetween('register_time', $timestampRange)
                ->where('deleted', 0)
                ->pluck('user_id')->toArray();
        }

        $onlineUsers = app('model_user_login_date')
            ->select('user_id', 'channel_stage_id', 'is_next_day_login', 'is_third_day_login', 'is_seven_day_login', 'is_fifteen_day_login', 'is_thirtieth_day_login')
            ->whereIn('user_id', $userIds)
            ->get()->toArray();

        return $onlineUsers;
    }

    //临时方法（2021-11-17）
    public function tmp($date = null)
    {
        if (empty($date)) $date = date('Y-m-d');
        $timestampRange = DateTime::getDateTimestampRange($date);

        //查询所有新用户id
        $newUserIds = app('model_user')
            ->select('user_id')
            ->whereBetween('register_time', $timestampRange)
            ->pluck('user_id', 'user_id')->toArray();

        //查询所有渠道分期用户id
        $stageUserIds = app('model_user')
            ->select('channel_stage_id', 'user_id')
            ->where('channel_stage_id', '>', 0)
            ->pluck('channel_stage_id', 'user_id')->toArray();

        //查询所有渠道分期
        $stages = app('model_channel_stage')
            ->select('id as channel_stage_id', 'channel_id')
            ->get()->toArray();
        $stages = array_column($stages, null, 'channel_stage_id');

        foreach ($stages as &$stage) {
            $stage['date'] = $date;
            $stage['recharge_user_num'] = [];
            $stage['recharge_count'] = 0;
            $stage['recharge_amount'] = 0;
        }
        //增加非渠道数据记录，渠道和分期id都为0
        $stages[0] = $stages[1];
        $stages[0]['channel_id'] = 0;
        $stages[0]['channel_stage_id'] = 0;

        //新老用户数据分别用一个二维数组保存所有统计数据
        $newUserData = $oldUserData = $stages;

        //充值记录
        $rechargeRecords = $this->rechargeRecord($timestampRange);
        foreach ($rechargeRecords as $v) {
            $userId = $v['user_id'];
            $stageId = $this->getStageIdByUserId($stageUserIds, $userId);
            if (isset($newUserIds[$userId])) {
                $newUserData[$stageId]['recharge_user_num'][$userId] = 0;
                $newUserData[$stageId]['recharge_count']++;
                $newUserData[$stageId]['recharge_amount'] += $v['money'];
            } else {
                $oldUserData[$stageId]['recharge_user_num'][$userId] = 0;
                $oldUserData[$stageId]['recharge_count']++;
                $oldUserData[$stageId]['recharge_amount'] += $v['money'];
            }
        }

        foreach ($newUserData as $v) {
            $v['recharge_user_num'] = count($v['recharge_user_num']);
            $v['is_new_user'] = 1;
            $res = app('model_platform_everyday_data')->getRow('id', [
                'date' => $v['date'],
                'channel_id' => $v['channel_id'],
                'channel_stage_id' => $v['channel_stage_id'],
                'is_new_user' => $v['is_new_user'],
            ]);
            if ($res) {
                app('model_platform_everyday_data')->edit(['id' => $res->id], $v);
            } else {
                app('model_platform_everyday_data')->add($v);
            }
        }
        foreach ($oldUserData as $v) {
            $v['recharge_user_num'] = count($v['recharge_user_num']);
            $v['is_new_user'] = 0;
            $res = app('model_platform_everyday_data')->getRow('id', [
                'date' => $v['date'],
                'channel_id' => $v['channel_id'],
                'channel_stage_id' => $v['channel_stage_id'],
                'is_new_user' => $v['is_new_user'],
            ]);
            if ($res) {
                app('model_platform_everyday_data')->edit(['id' => $res->id], $v);
            } else {
                app('model_platform_everyday_data')->add($v);
            }
        }
    }

    /**
     * 统计汇总数据
     * @param $date
     * @return mixed
     */
    public function calcTotalStat($date)
    {
        $total = app('model_everyday_data_statistics')->select([
            DB::raw("SUM(`register_total`) AS register_total"),
            DB::raw("SUM(`recharge_total`) AS recharge_total"),
            DB::raw("SUM(`serial_total`) AS serial_total"),
            DB::raw("SUM(`withdraw`) AS withdraw"),
            DB::raw("SUM(`active`) AS active"),
            DB::raw("SUM(`send_red_packet`) AS send_red_packet"),
            DB::raw("SUM(`receive_red_packet`) AS receive_red_packet"),
            DB::raw("SUM(`give_gift`) AS give_gift"),
        ])
        ->whereBetween('date',[$date,date('Y-m-d')])
        ->get()
        ->toArray();
        return $total;
    }


    /**
     * 统计历史日数据脚本
     * @param $date
     * @return void
     */
    public function calcDayRange($date)
    {
        for ($i = 0; $i <= 304; $i++){
            $start = strtotime($date)+$i*86400;
            if ($start > time()){
                return;
            }
            $end = $start+86399;
            $temp = [];
            $day = date('Y-m-d',$start);
            $temp['recharge_total'] = $this->calcDayUserRecharge($start,$end);
            $temp['withdraw'] =$this->calcDayWithDraw($start,$end);
            $temp['active'] = $this->calcDayActive($start,$end);
            $temp['send_red_packet'] = $this->calcDaySendRed($start,$end)/100;
            $temp['receive_red_packet'] = $this->calcDayReceiveRed($start,$end)/100;
            $temp['give_gift'] = $this->calcDayGiveGift($start,$end)/100;

            app('model_everyday_data_statistics')->where('date',$day)
                ->update($temp);
            echo $day."已完成".PHP_EOL;
        }
    }

    /**
     * 单日提现成功金额
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDayWithDraw($start,$end)
    {
        return app('model_user_withdraw')->where('platform_status',2)
            ->where('status',2)
            ->whereBetween('operate_time',[$start,$end])
            ->where('operate_time','>',0)
            ->sum('money');
    }

    /**
     * 单日活跃人数
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDayActive($start,$end)
    {
        return app('model_user_day_login')->where('day',date('Y-m-d',$start))
            ->count('id');
    }

    /**
     * 单日发红包金额
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDaySendRed($start,$end)
    {
        $range = [date('Y-m-d H:i:s',$start),date('Y-m-d H:i:s',$end)];
        return app('model_private_chat_rp_record')->whereBetween('created_at',$range)
            ->where('status','<',3)
            ->sum('money');
    }

    /**
     * 单日收红包金额
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDayReceiveRed($start,$end)
    {
        $range = [date('Y-m-d H:i:s',$start),date('Y-m-d H:i:s',$end)];
        return app('model_private_chat_rp_record')->whereBetween('created_at',$range)
            ->where('status',1)
            ->sum('money');
    }

    /**
     * 单日直刷礼物金额
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDayGiveGift($start,$end)
    {
        return app('model_give_gift')->whereBetween('add_time',[$start,$end])
            ->where('type','<>',5)
            ->sum('total_price');
    }

    /**
     * 单日直刷礼物金额
     * @param $start
     * @param $end
     * @return mixed
     */
    public function calcDayUserRecharge($start,$end)
    {
        return app('model_user_recharge')
            ->whereBetween('pay_time', [$start,$end])
            ->where('type','<>',3)
            ->where('status', 2)
            ->sum('money');
    }
}
